"""
Case Type   : 服务端工具
Case Name   : 脏页数不为0时停止数据库成功
Create At   : 2024/10/23
Owner       : @xiaqi14
Description :
    1.设置参数
    2.执行sql语句
    3.脏页数不为0
    4.停止数据库
    5.清理环境
Expect      :
    1.设置参数成功
    2.执行sql语句成功
    3.查询成功
    4.停止数据库成功
    5.清理环境成功
History     :
"""
import os
import unittest
from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

COMMONSH = CommonSH("PrimaryDbUser")


@unittest.skipIf(3 != COMMONSH.get_node_num(), '非1+2环境不执行')
class Tools(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.common = Common()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.Constant = Constant()
        self.primary_sh = CommonSH('PrimaryDbUser')
        self.primary_node = Node('PrimaryDbUser')
        self.env_path = macro.DB_ENV_PATH
        self.sta_node = Node('Standby1DbUser')
        self.dn_path = macro.DB_INSTANCE_PATH
        self.standby_sh = CommonSH('Standby1DbUser')

    def test_gs_om(self):
        text = '----step1:设置参数log_pagewriter=on，bgwriter_delay=10s expect:成功----'
        self.log.info(text)
        excute_cmd = f'''
                    source {self.env_path}
                    gs_guc reload -N all -I all -c "log_pagewriter = on"
                    gs_guc reload -N all -I all -c "bgwriter_delay = 10s"
                    '''
        self.log.info(excute_cmd)
        msg = self.primary_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.assertEqual(2, msg.count(self.Constant.GSGUC_SUCCESS_MSG), '执行失败' + text)

        text = '----step2:执行sql语句 expect:成功----'
        self.log.info(text)
        sql_cmd = f'''
                    declare
                        tb_name text;
                        begin
                            for i in 0..100 loop
                                tb_name := 't_gs_om_0126_' || i;
                                execute 'drop table if exists ' || tb_name || ';';
                                execute 'create table ' || tb_name ||'
                                (id1 varchar unique,id2 int primary key,id3 int);';
                                execute 'insert into '
                                || tb_name 
                                || ' values(generate_series(1,5000),
                                generate_series(1,5000),generate_series(1,5000));';
                            end loop;
                        end; 
                    '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('ANONYMOUS BLOCK EXECUTE', result, '执行失败' + text)

        text = '----step3:脏页数不为0 expect:成功----'
        self.log.info(text)
        sql_cmd = f'''
                    select pg_sleep(10);
                   select remain_dirty_page_num from dbe_perf.global_pagewriter_status;
                    '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertNotEqual('0', result.splitlines()[-2].strip(), '执行失败' + text)

        text = '----step4 停止数据库 expect:成功----'
        self.log.info(text)
        excute_cmd = f'''
                        source {self.env_path}
                        gs_om -t stop 
                        '''
        self.log.info(excute_cmd)
        msg = self.primary_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.assertIn(self.Constant.GS_OM_STOP_SUCCESS_MSG, msg,'执行失败' + text)

    def tearDown(self):
        text = "----step5：恢复环境 expect:成功----"
        self.log.info(text)
        excute_cmd = f'''
                source {self.env_path}
                gs_om  -t  start
                    '''
        self.log.info(excute_cmd)
        msg = self.primary_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.assertIn('Successfully started', msg,'执行失败' + text)
        sql_cmd = f'''
                    declare
                        tb_name text;
                        begin
                            for i in 0..100 loop
                                tb_name := 't_gs_om_0126_' || i;
                                execute 'drop table if exists ' || tb_name || ';';
                                execute 'create table ' || tb_name ||'
                                (id1 varchar unique,id2 int primary key,id3 int);';
                                execute 'insert into '
                                || tb_name 
                                || ' values(generate_series(1,5000),
                                generate_series(1,5000),generate_series(1,5000));';
                            end loop;
                        end; 
                    '''
        result = self.primary_sh.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('ANONYMOUS BLOCK EXECUTE', result, '执行失败' + text)
        excute_cmd = f'''
                    source {self.env_path}
                    gs_guc reload -N all -I all -c "log_pagewriter = off"
                    gs_guc reload -N all -I all -c "bgwriter_delay = 2s"
                    '''
        self.log.info(excute_cmd)
        msg = self.primary_node.sh(excute_cmd).result()
        self.log.info(msg)
        self.assertEqual(2, msg.count(self.Constant.GSGUC_SUCCESS_MSG), '执行失败' + text)
        self.log.info(f'----{os.path.basename(__file__)} end----')
